using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.IO;
using System.Windows.Forms;
using twistmachinev2.Entity;

namespace twistmachinev2.Repository
{
    /// <summary>
    /// SQLite数据库通用操作仓储类
    /// </summary>
    public class SqliteRepository : IDisposable
    {
        private SqlSugarClient _db;
        private bool _isConnected = false;
        private bool _disposed = false;

        /// <summary>
        /// 默认构造函数
        /// </summary>
        public SqliteRepository()
        {
            string connectionString = BaseData.OPCUADatabaseConnectionString;
            InitializeDatabase(connectionString);
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        public SqliteRepository(string connectionString)
        {
            InitializeDatabase(connectionString);
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="dbPath">数据库文件路径</param>
        public SqliteRepository(string dbPath, bool isFilePath)
        {
            if (isFilePath)
            {
                string connectionString = $"Data Source={dbPath}";
                InitializeDatabase(connectionString);
            }
            else
            {
                InitializeDatabase(dbPath);
            }
        }

        /// <summary>
        /// 初始化数据库连接
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        private void InitializeDatabase(string connectionString)
        {
            try
            {
                _db = new SqlSugarClient(new ConnectionConfig()
                {
                    ConnectionString = connectionString,
                    DbType = SqlSugar.DbType.Sqlite,
                    IsAutoCloseConnection = true,
                    InitKeyType = InitKeyType.Attribute
                });

                // 测试连接
                _db.Ado.CheckConnection();
                _isConnected = true;
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"数据库连接失败: {ex.Message}", ex);
            }
        }

        #region 连接管理

        /// <summary>
        /// 检查数据库连接状态
        /// </summary>
        public bool IsConnected => _isConnected && _db != null && !_disposed;

        /// <summary>
        /// 获取SqlSugar客户端实例
        /// </summary>
        /// <returns>SqlSugar客户端</returns>
        public SqlSugarClient GetDbClient()
        {
            CheckConnection();
            return _db;
        }

        /// <summary>
        /// 检查连接状态
        /// </summary>
        private void CheckConnection()
        {
            if (!IsConnected)
            {
                throw new InvalidOperationException("数据库未连接或连接已断开");
            }
        }

        #endregion



        #region 增删改查操作

        /// <summary>
        /// 通用插入方法
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="entity">要插入的实体</param>
        /// <returns>插入成功返回true</returns>
        public bool Insert<T>(T entity) where T : class, new()
        {
            CheckConnection();
            try
            {
                return _db.Insertable(entity).ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"插入数据失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 通用批量插入方法
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="entities">要插入的实体列表</param>
        /// <returns>插入成功的行数</returns>
        public int InsertRange<T>(List<T> entities) where T : class, new()
        {
            CheckConnection();
            try
            {
                return _db.Insertable(entities).ExecuteCommand();
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"批量插入数据失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 通用删除方法
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="whereExpression">删除条件</param>
        /// <returns>删除的行数</returns>
        public int Delete<T>(Expression<Func<T, bool>> whereExpression) where T : class, new()
        {
            CheckConnection();
            try
            {
                return _db.Deleteable<T>().Where(whereExpression).ExecuteCommand();
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"删除数据失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 通用根据主键删除方法
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="id">主键值</param>
        /// <returns>删除成功返回true</returns>
        public bool DeleteById<T>(object id) where T : class, new()
        {
            CheckConnection();
            try
            {
                return _db.Deleteable<T>().In(id).ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"根据ID删除数据失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 通用更新方法
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="entity">要更新的实体</param>
        /// <returns>更新成功返回true</returns>
        public bool Update<T>(T entity) where T : class, new()
        {
            CheckConnection();
            try
            {
                return _db.Updateable(entity).ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"更新数据失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 通用条件更新方法
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="entity">要更新的实体</param>
        /// <param name="whereExpression">更新条件</param>
        /// <returns>更新的行数</returns>
        public int Update<T>(T entity, Expression<Func<T, bool>> whereExpression) where T : class, new()
        {
            CheckConnection();
            try
            {
                return _db.Updateable(entity).Where(whereExpression).ExecuteCommand();
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"条件更新数据失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 通用查询单个实体方法
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="whereExpression">查询条件</param>
        /// <returns>查询到的实体</returns>
        public T GetSingle<T>(Expression<Func<T, bool>> whereExpression) where T : class, new()
        {
            CheckConnection();
            try
            {
                return _db.Queryable<T>().Where(whereExpression).First();
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"查询单个实体失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 通用根据主键查询方法
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="id">主键值</param>
        /// <returns>查询到的实体</returns>
        public T GetById<T>(object id) where T : class, new()
        {
            CheckConnection();
            try
            {
                return _db.Queryable<T>().InSingle(id);
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"根据ID查询数据失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 通用查询列表方法
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="whereExpression">查询条件</param>
        /// <returns>查询到的实体列表</returns>
        public List<T> GetList<T>(Expression<Func<T, bool>> whereExpression = null) where T : class, new()
        {
            CheckConnection();
            try
            {
                var query = _db.Queryable<T>();
                if (whereExpression != null)
                {
                    query = query.Where(whereExpression);
                }
                return query.ToList();
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"查询列表数据失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 通用分页查询方法
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="pageIndex">页码（从1开始）</param>
        /// <param name="pageSize">每页大小</param>
        /// <param name="totalCount">总记录数</param>
        /// <param name="whereExpression">查询条件</param>
        /// <returns>分页查询结果</returns>
        public List<T> GetPageList<T>(int pageIndex, int pageSize, out int totalCount,
            Expression<Func<T, bool>> whereExpression = null) where T : class, new()
        {
            CheckConnection();
            try
            {
                var query = _db.Queryable<T>();
                if (whereExpression != null)
                {
                    query = query.Where(whereExpression);
                }
                totalCount = query.Count();
                return query.ToPageList(pageIndex, pageSize, ref totalCount);
            }
            catch (Exception ex)
            {
                totalCount = 0;
                throw new InvalidOperationException($"分页查询数据失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 执行原生SQL查询
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>查询结果DataTable</returns>
        public DataTable ExecuteSql(string sql, params SugarParameter[] parameters)
        {
            CheckConnection();
            try
            {
                return _db.Ado.GetDataTable(sql, parameters);
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"执行SQL查询失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 执行原生SQL命令
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>影响的行数</returns>
        public int ExecuteCommand(string sql, params SugarParameter[] parameters)
        {
            CheckConnection();
            try
            {
                return _db.Ado.ExecuteCommand(sql, parameters);
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"执行SQL命令失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 创建表（如果不存在）
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <returns>创建成功返回true</returns>
        public bool CreateTableIfNotExists<T>() where T : class, new()
        {
            CheckConnection();
            try
            {
                _db.CodeFirst.InitTables<T>();
                return true;
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"创建表失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 获取所有表信息
        /// </summary>
        /// <returns>表信息列表</returns>
        public List<DbTableInfo> GetTableInfoList()
        {
            CheckConnection();
            try
            {
                return _db.DbMaintenance.GetTableInfoList();
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"获取表信息失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 检查表是否存在
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns>存在返回true</returns>
        public bool IsTableExists(string tableName)
        {
            CheckConnection();
            try
            {
                return _db.DbMaintenance.IsAnyTable(tableName);
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"检查表是否存在失败: {ex.Message}", ex);
            }
        }

        #endregion

        #region 事务操作

        /// <summary>
        /// 开始事务
        /// </summary>
        public void BeginTransaction()
        {
            CheckConnection();
            _db.BeginTran();
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        public void CommitTransaction()
        {
            CheckConnection();
            _db.CommitTran();
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        public void RollbackTransaction()
        {
            CheckConnection();
            _db.RollbackTran();
        }

        /// <summary>
        /// 执行事务操作
        /// </summary>
        /// <param name="action">事务操作</param>
        public void ExecuteTransaction(Action action)
        {
            CheckConnection();
            _db.BeginTran();
            try
            {
                action();
                _db.CommitTran();
            }
            catch
            {
                _db.RollbackTran();
                throw;
            }
        }

        /// <summary>
        /// 执行事务操作（带返回值）
        /// </summary>
        /// <typeparam name="T">返回值类型</typeparam>
        /// <param name="func">事务操作</param>
        /// <returns>操作结果</returns>
        public T ExecuteTransaction<T>(Func<T> func)
        {
            CheckConnection();
            _db.BeginTran();
            try
            {
                T result = func();
                _db.CommitTran();
                return result;
            }
            catch
            {
                _db.RollbackTran();
                throw;
            }
        }

        #endregion

        #region IDisposable实现

        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        /// <summary>
        /// 释放资源
        /// </summary>
        /// <param name="disposing">是否释放托管资源</param>
        protected virtual void Dispose(bool disposing)
        {
            if (!_disposed)
            {
                if (disposing)
                {
                    _db?.Dispose();
                    _db = null;
                    _isConnected = false;
                }
                _disposed = true;
            }
        }

        /// <summary>
        /// 析构函数
        /// </summary>
        ~SqliteRepository()
        {
            Dispose(false);
        }

        #endregion
    }
}